/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2010 Oracle and/or its affiliates. All rights reserved.
 *
 * Oracle and Java are registered trademarks of Oracle and/or its affiliates.
 * Other names may be trademarks of their respective owners.
 *
 * The contents of this file are subject to the terms of either the GNU
 * General Public License Version 2 only ("GPL") or the Common
 * Development and Distribution License("CDDL") (collectively, the
 * "License"). You may not use this file except in compliance with the
 * License. You can obtain a copy of the License at
 * http://www.netbeans.org/cddl-gplv2.html
 * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
 * specific language governing permissions and limitations under the
 * License.  When distributing the software, include this License Header
 * Notice in each file and include the License file at
 * nbbuild/licenses/CDDL-GPL-2-CP.  Oracle designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Oracle in the GPL Version 2 section of the License file that
 * accompanied this code. If applicable, add the following below the
 * License Header, with the fields enclosed by brackets [] replaced by
 * your own identifying information:
 * "Portions Copyrighted [year] [name of copyright owner]"
 *
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution
 * under the [CDDL or GPL Version 2] license." If you do not indicate a
 * single choice of license, a recipient has the option to distribute
 * your version of this file under either the CDDL, the GPL Version 2 or
 * to extend the choice of license to its licensees as provided above.
 * However, if you add GPL Version 2 code and therefore, elected the GPL
 * Version 2 license, then the option applies only if the new code is
 * made subject to such option by the copyright holder.
 *
 * Contributor(s):
 *
 * Portions Copyrighted 2008 Sun Microsystems, Inc.
 * 
 * Portions Copyrighted 2013 Nicolas Ribot (modifications to cope with procedures)
 */
package org.nico.db.sql.editor;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.netbeans.api.db.explorer.DatabaseConnection;
import org.netbeans.modules.db.metadata.model.api.Catalog;
import org.netbeans.modules.db.metadata.model.api.Metadata;
import org.netbeans.modules.db.metadata.model.api.Procedure;
import org.netbeans.modules.db.metadata.model.api.Schema;
import org.openide.util.RequestProcessor;
import org.openide.util.TaskListener;
import org.netbeans.api.progress.ProgressHandle;
import org.netbeans.api.progress.ProgressHandleFactory;
import org.openide.util.NbBundle;

/**
 * A singleton storing Postgis Metadata for all connections: version, list of
 * functions, schema. It Retrieves documentation information from the internet
 * and postgis version by issuing a SQL query to the current connexion
 *
 * TODO: I18N
 *
 * @author nicolas Ribot
 */
public class PostgisMetadata implements Runnable {

    private final static RequestProcessor RP = new RequestProcessor("interruptible tasks", 1, true); //NOI18N
    private static final Logger LOGGER = Logger.getLogger(PostgisMetadata.class.getName());
    public static final String POSTGIS_PROC = "postgis_version";  //NOI18N
    /**
     * the name of the postgis topology schema
     */
    public static final String POSTGIS_TOPO_SCHEMA = "topology";  //NOI18N
    public static final String DOC_URL = "http://postgis.net/docs/manual-VERSION/PROC_NAME.html";  //NOI18N
    private static PostgisMetadata instance = null;
    public static final String SRC_DOC_URL = "http://svn.osgeo.org/postgis/tags/VERSION.0/doc/HTML_FILE"; //NOI18N
    /**
     * The several postgis connections information, remembered here to avoid
     * reloading functions list from web.
     */
    private Map<String, PgObj> pgisInfos;
    /**
     * the current PgObj we are working on. It will contain version and
     * functions map
     */
    private PgObj curPgObj;

    /**
     * A little object to store our params
     */
    public class PgObj {
        // the name of the connection is used as internal name, it will also
        // be the Map key

        public String name;
        public Schema schema;
        /**
         * The postgis topology schema, if available. Allows autocompletion of
         * postgis topology schema functions
         */
        public Schema topoSchema;
        public String version;
        /**
         * The map of Postgis functions: key is the name of the proceure,
         * lowercase. value is a list of postgis functions having this name
         * (manages overloaded functions)
         */
        public Map<String, List<PostgisFunction>> functionsDocMap;
        // to bypass actions if not a PG database
        public Boolean isPg;
        /**
         * true if default version is used, ie: unable to get version from
         * current connection
         */
        public boolean defaultVersion;

        public PgObj() {
            this.version = "2.0"; //NOI18N
            this.defaultVersion = true;
            this.isPg = false;
        }

        public PgObj(String name, String version) {
            this();
            this.version = version;
            this.name = name;
        }

        public Map<String, List<PostgisFunction>> getFunctionsDocMap() {
            return functionsDocMap;
        }

        /**
         *
         * @return
         */
        public String getSchemaName() {
            return schema == null ? "" : schema.getName();
        }

        /**
         * Returns true if a schema object is available and its name is not
         * equal to the given schema Name
         *
         * @param schemaName the name of the schema to test the internal schema
         * against
         * @return
         */
        public boolean postgisAvailable(String schemaName) {
            if (this.schema == null) {
                return false;
            }

            return !this.schema.getName().equals(schemaName);
        }

        /**
         * Returns true if a topology schema object is available and its name is
         * not equal to the given schema Name
         *
         * @param schemaName the name of the schema to test the internal schema
         * against
         * @return
         */
        public boolean postgisTopoAvailable(String schemaName) {
            if (this.topoSchema == null) {
                return false;
            }

            return !this.topoSchema.getName().equals(schemaName);
        }

        /**
         *
         * @param procName
         * @return the postgis procedure with lower case name equals to param,
         * or a newly created Postgis procedure if not found (to allow correct
         * display of other database procedures)
         */
        public List<PostgisFunction> getPostgisFunction(String procName) {
            PostgisFunction pf = new PostgisFunction(procName);
            List<PostgisFunction> res = PostgisFunction.toList(pf);

            if (this.functionsDocMap != null && this.functionsDocMap.containsKey(procName)) {
                res = this.functionsDocMap.get(procName);
            }

            return res;
        }
    };

    // Exists only to defeat instantiation: singleton
    protected PostgisMetadata() {
        pgisInfos = new HashMap<String, PgObj>();
    }

    public static PostgisMetadata getInstance() {
        if (instance == null) {
            instance = new PostgisMetadata();
        }
        return instance;
    }

    public PgObj getPgObj() {
        return curPgObj;
    }

    private void delegateFetchData() {
        final RequestProcessor.Task theTask = RP.create(this);

        final ProgressHandle ph = ProgressHandleFactory.createHandle(
                NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.MSG.FetchingPostgisFunctions"), theTask);
        theTask.addTaskListener(new TaskListener() {
            public void taskFinished(org.openide.util.Task task) {
                //make sure that we get rid of the ProgressHandle
                //when the task is finished
                ph.finish();
            }
        });

        //start the progresshandle the progress UI will show 500s after
        ph.start();

        //this actually start the task.
        theTask.schedule(0);
    }

    /**
     * Fetches postgis list of functions (camelcase names) from live postgis
     * sources (internet). Also fetches list of topology functions
     *
     * TODO: replace by a XML parser taking entities into account (add dtd ?)
     * see code at the end
     */
    private Map<String, List<PostgisFunction>> fetchData() {
        Map<String, List<PostgisFunction>> res = new HashMap<String, List<PostgisFunction>>();
        String su = PostgisMetadata.SRC_DOC_URL.replace("VERSION", this.curPgObj.version).replace("HTML_FILE", "reference.xml");  //NOI18N
        try {
            LOGGER.info(NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.MSG.FetchingPostgisFunctions"));
            URL functionDefUrl = new URL(su);
            Collection<URL> funcDocs = getFunctionsDocUrls(functionDefUrl);

            for (URL u : funcDocs) {
                //res.putAll(getFunctionsFromUrl(u));
                getFunctionsFromUrl(u, res);
            }

            // keep individual Maps of function to 
            // Fetches raster functions: their definitions is in the http://svn.osgeo.org/postgis/tags/2.1.0/doc/extras_topology.xml file
            su = PostgisMetadata.SRC_DOC_URL.replace("VERSION", this.curPgObj.version).replace("HTML_FILE", "reference_raster.xml");  //NOI18N
            functionDefUrl = new URL(su);
            //res.putAll(getFunctionsFromUrl(functionDefUrl));
            getFunctionsFromUrl(functionDefUrl, res);

            // Fetches topology functions: their definitions is in the http://svn.osgeo.org/postgis/tags/2.1.0/doc/reference_raster.xml file
            su = PostgisMetadata.SRC_DOC_URL.replace("VERSION", this.curPgObj.version).replace("HTML_FILE", "extras_topology.xml");  //NOI18N
            functionDefUrl = new URL(su);
            //res.putAll(getFunctionsFromUrl(functionDefUrl));
            getFunctionsFromUrl(functionDefUrl, res);
        } catch (Exception ex) {
            LOGGER.severe(
                    NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.MSG.FunctionListError",
                            su, ex.toString()));
            loadDefaultFunctions();
        }

        return res;
    }

    private Collection<URL> getFunctionsDocUrls(URL functionDefUrl) {
        ArrayList<URL> res = new ArrayList<URL>();

        try {
            BufferedReader reader = new BufferedReader(new InputStreamReader(functionDefUrl.openStream()));
            String line = null;
            while ((line = reader.readLine()) != null) {
                Pattern pattern = Pattern.compile("&(.*?);"); //NOI18N
                Matcher matcher = pattern.matcher(line);
                if (matcher.find()) {
                    res.add(new URL(PostgisMetadata.SRC_DOC_URL
                            .replace("HTML_FILE", matcher.group(1) + ".xml") //NOI18N
                            .replace("VERSION", this.curPgObj.version))); //NOI18N
                }
            }
            reader.close();

        } catch (Exception ex) {
        }
        return res;
    }

    /**
     * Returns a Map storing the list of PostgisFunctions read from given URL
     * Map key is the function name, lowercased. Value is a list of functios
     * having this name.
     *
     * @param u the URL of Postgis Doc WML file to fetch list of functions from
     * @return the Map of postgis functions
     */
//    private Map<String, List<PostgisFunction>> getFunctionsFromUrl(URL u) {
    private void getFunctionsFromUrl(URL u, Map<String, List<PostgisFunction>> res) {

        //HashMap<String, List<PostgisFunction>> res = new HashMap<String, List<PostgisFunction>>();
        if (u != null) {
            try {

                BufferedReader reader = new BufferedReader(new InputStreamReader(u.openStream()));
                String line = null;
                StringBuilder b = new StringBuilder();
                while ((line = reader.readLine()) != null) {
                    b.append(line);
                }
                reader.close();

                String p = "<refentry id=\"(.*?)\">(.*?)</refsynopsisdiv>";
                //String p = "<funcprototype>(.*?)</funcprototype>";
                //String p = "<funcdef>(.*?)<function>(.+?)</function></funcdef>\\s*(<paramdef>(.*?)</paramdef>)*";
                Pattern pattern = Pattern.compile(p);
                Matcher matcher = pattern.matcher(b.toString());
                String funcHtmlDoc = "";
                String funcName = "";
                String funcReturnType = "";
                while (matcher.find()) {
                // function id = html doc name
                funcHtmlDoc = matcher.group(1).trim();
                // function block
                    String fullFnDef = matcher.group(2).trim();

                    Pattern p1 = Pattern.compile("<funcdef>(.*?)<function>");
                    Matcher m1 = p1.matcher(fullFnDef);
                    if (m1.find()) {
                        funcReturnType = m1.group(1).trim();
                    }
                    p1 = Pattern.compile("<function>(.*?)</function>");
                    m1 = p1.matcher(fullFnDef);
                    if (m1.find()) {
                        funcName = m1.group(1).trim();
                    }
                    p1 = Pattern.compile("<paramdef\\b[^>]*>(.*?)</paramdef>");
                    m1 = p1.matcher(fullFnDef);
                    ArrayList<PostgisFunctionParam> params = new ArrayList<PostgisFunctionParam>();
                    while (m1.find()) {
                        String s = m1.group(1).trim();
                        Pattern p2 = Pattern.compile("<type>(.*?)</type>\\s*<parameter>(.*?)</parameter>");
                        Matcher m2 = p2.matcher(s);
                        if (m2.find()) {
                            params.add(new PostgisFunctionParam(m2.group(2).trim(), m2.group(1).trim()));
                        }
                    }
                    PostgisFunction pf = new PostgisFunction(funcName, funcReturnType, params, funcHtmlDoc);

                    // To generate the code to build default fn list used when internet access is not available:
                    // generates Java code to build Functions Map in stdout
                    //System.out.println(pf.toJavaCode());

                    if (res.containsKey(funcName.toLowerCase())) {
                        res.get(funcName.toLowerCase()).add(pf);
                    } else {
                        res.put(funcName.toLowerCase(), PostgisFunction.toList(pf));
                    }
                }

            } catch (Exception ex) {
            }
        }
        //return res;
    }

    /**
     * Fetches postgis version from database connexion and list of functions
     * from live postgis sources.
     *
     * TODO: separate actions into tasks: version and function
     *
     * @param con the database connection
     */
    public void fetchPostGISVersion(DatabaseConnection con) {
        this.curPgObj = pgisInfos.get(con.getName());

        if (this.curPgObj == null) {
            this.curPgObj = new PgObj(con.getName(), "2.0");
        }

        // test if connection is a postgresql one
        this.curPgObj.isPg = con.getDriverClass().contains("org.postgresql");

        if (!this.curPgObj.isPg) {
            // Not a PostGIS connection
            return;
        }

        // strangely, the JDBC connection can be null though a valid connection is present in netbeans:
        // retry to get version from database, in this case
        if (this.curPgObj.defaultVersion) {
            // gets info live
            String s = this.curPgObj.schema == null ? "public" : this.curPgObj.schema.getName(); //NOI18N
            Connection conn = con.getJDBCConnection();
            if (conn != null) {
                try {
                    Statement stmt = conn.createStatement();
                    String q = "select " + s + ".postgis_version()";  //NOI18N
                    ResultSet rs = stmt.executeQuery(q);
                    rs.next();

                    this.curPgObj.name = con.getName();
                    this.curPgObj.version = rs.getString(1).split(" ")[0];
                    this.curPgObj.defaultVersion = false;
                    this.pgisInfos.put(con.getName(), this.curPgObj);
                    LOGGER.info(NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.MSG.GettingPostgisVersion",
                            this.curPgObj.version));

                    stmt.close();
                } catch (SQLException ex) {
                    LOGGER.severe(NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.ERR.GettingPostgisVersion", ex.toString()));
                }
            } else {
                LOGGER.severe(NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.ERR.NullJDBC"));
            }
        }

        // then loads functions data
        PgObj o = pgisInfos.get(con.getName());
        if (o != null && o.functionsDocMap != null) {
            // reuse info
            LOGGER.fine(NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.MSG.PostgisInfoCached", con.getName()));
            this.curPgObj = o;
        } else {
            // initialize default function list and fetch functions list live from postgis website
            // in a separate thread
            this.loadDefaultFunctions();
            this.delegateFetchData();
        }
    }

    /**
     * gets the postgis schema for the current pgObj metadata by looking at
     * given Metadata object to find the POSTGIS_PROC function
     */
    public Schema getPostgisSchema(Metadata metadata) {
        if (this.curPgObj != null && this.curPgObj.schema == null) {
            for (Catalog c : metadata.getCatalogs()) {
                for (Schema s : c.getSchemas()) {
                    for (Procedure p : s.getProcedures()) {
                        if (PostgisMetadata.POSTGIS_PROC.equals(p.getName())) {
                            this.curPgObj.schema = s;

                            LOGGER.fine(NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.MSG.GettingPostgisSchema", this.curPgObj.schema.getName(), this.curPgObj.name));
                            return s;
                        }
                    }
                }
            }
        } else {
            //LOGGER.severe("Cannot get postgis schema: curPgObj is null :(");
        }
        return null;
    }

    /**
     * gets the postgis topology schema for the current pgObj metadata by
     * looking if topology schema exists in the database
     */
    public Schema getPostgisTopologySchema(Metadata metadata) {
        if (this.curPgObj != null && this.curPgObj.topoSchema == null) {
            for (Catalog c : metadata.getCatalogs()) {
                for (Schema s : c.getSchemas()) {
                    if (PostgisMetadata.POSTGIS_TOPO_SCHEMA.equals(s.getName())) {
                        this.curPgObj.topoSchema = s;

                        LOGGER.fine(NbBundle.getMessage(PostgisMetadata.class, "PostgisMetadata.MSG.GettingPostgisSchema", this.curPgObj.schema.getName(), this.curPgObj.name));
                        return s;
                    }
                }
            }
        } else {
            //LOGGER.severe("Cannot get postgis schema: curPgObj is null :(");
        }
        return null;
    }

    /**
     * Runs the
     */
    @Override
    public void run() {
        Map<String, List<PostgisFunction>> liveFnDef = this.fetchData();

        if (liveFnDef != null && !liveFnDef.isEmpty()) {
            this.curPgObj.functionsDocMap = liveFnDef;
            // refreshes our PgObj
            this.pgisInfos.put(this.curPgObj.name, this.curPgObj);
        }
    }

    /**
     * Use dumpFn method to generate this code...
     */
    private void loadDefaultFunctions() {
        LOGGER.info("loading default PostGIS functions map...");

        if (this.curPgObj.functionsDocMap == null) {
            this.curPgObj.functionsDocMap = DefaultFunctionList.getList();
        }
    }
}
//        try {
//
//            DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
//            DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
//            URL u = new URL("http://svn.osgeo.org/postgis/tags/2.1.0/doc/reference_accessor.xml");
//            Document doc = docBuilder.parse(u.openStream());
//
//            // normalize text representation
//            doc.getDocumentElement().normalize();
//            System.out.println("Root element of the doc is "
//                    + doc.getDocumentElement().getNodeName());
//
//
//            NodeList listOfFunctions = doc.getElementsByTagName("refname");
//            int totalFunctions = listOfFunctions.getLength();
//            System.out.println("Total no of functions : " + totalFunctions);
//
//            for (int s = 0; s < listOfFunctions.getLength(); s++) {
//                Node fnNode = listOfFunctions.item(s);
//                System.out.println("Fn: " + fnNode.getNodeValue().trim());
//
//            }//end of for loop with s var
//
//        } catch (SAXParseException err) {
//            System.out.println("** Parsing error" + ", line "
//                    + err.getLineNumber() + ", uri " + err.getSystemId());
//            System.out.println(" " + err.getMessage());
//
//        } catch (SAXException e) {
//            Exception x = e.getException();
//            ((x == null) ? e : x).printStackTrace();
//
//        } catch (Throwable t) {
//            t.printStackTrace();
    //        }
